package com.scalar.chiptrack.finance.costCode.dao;

import com.scalar.chiptrack.db.DBTransaction;
import com.scalar.chiptrack.db.DataSource;
import com.scalar.chiptrack.finance.costCode.CostCodeMaster;
import com.scalar.chiptrack.finance.costCode.forms.CostCodeMasterForm;
import com.scalar.chiptrack.operations.release.ReleaseLot;
import com.scalar.ScalarException;

import java.util.ArrayList;
import java.sql.*;

/**
 * User: KP
 * Date: Aug 3, 2007
 * Time: 10:32:41 AM
 */

public class CostCodeMasterDAO
{

    public static ArrayList loadPackageCostCodes( DBTransaction dbTransaction ) throws ScalarException
    {
        Connection conn             = null;
        ResultSet rs                = null;
        PreparedStatement pstmt     = null;
        CostCodeMaster costCodeMaster = null;
        ArrayList packageList       = new ArrayList();

        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement( "SELECT DISTINCT ISNULL( COST_CODE_ID, -2) AS AUTO_ID, PROCESS_PARAMETER_VALUE, COST_CODE FROM PROCESS_PARAMETER_VALUES PPV LEFT OUTER JOIN COST_CODE_MASTER CCM ON  PPV.PROCESS_PARAMETER_VALUE = COST_CODE_NO WHERE PROCESS_TYPE = 'ASSEMBLY' AND PROCESS_PARAMETER = 'PACKAGE ID' AND PROCESS_PARAMETER_VALUE IS NOT NULL AND LEN( PROCESS_PARAMETER_VALUE ) > 0 ORDER BY PROCESS_PARAMETER_VALUE" );
            rs = pstmt.executeQuery();

            while ( rs.next() )
            {
                costCodeMaster = new CostCodeMaster();

                costCodeMaster.setAutoId        ( rs.getInt( "AUTO_ID" ) );
                costCodeMaster.setPackageType   ( rs.getString( "PROCESS_PARAMETER_VALUE" ) );
                costCodeMaster.setCostCode      ( rs.getString( "COST_CODE" ) );
                costCodeMaster.setType          ( "PACKAGE" );

                packageList.add( costCodeMaster );
            }

        }
        catch ( SQLException sql)
        {
            sql.printStackTrace();
            throw new ScalarException( "Could not get Package Cost Code Details ", sql );
        }
        finally
        {
            if ( rs != null )
            {
                try { rs.close(); } catch (SQLException igonore) { }
            }

            if ( pstmt != null )
            {
                try { pstmt.close(); } catch (SQLException igonore) { }
            }

            if ( conn != null )
            {
                try { conn.close(); } catch (SQLException igonore) { }
            }
        }
        return packageList;
    }


    public static ArrayList loadPinCostCodes( DBTransaction dbTransaction ) throws ScalarException
    {
        Connection conn             = null;
        ResultSet rs                = null;
        PreparedStatement pstmt     = null;
        CostCodeMaster costCodeMaster = null;
        ArrayList pinCountList      = new ArrayList();

        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement( "SELECT ISNULL( COST_CODE_ID, -2) AS AUTO_ID, PROCESS_PARAMETER_VALUE, COST_CODE FROM PROCESS_PARAMETER_VALUES PPV LEFT OUTER JOIN COST_CODE_MASTER PCC ON  PPV.PROCESS_PARAMETER_VALUE = COST_CODE_NO WHERE PROCESS_TYPE = 'ASSEMBLY' AND PROCESS_PARAMETER = 'PIN' AND PROCESS_PARAMETER_VALUE IS NOT NULL AND LEN( PROCESS_PARAMETER_VALUE ) > 0 ORDER BY CAST( PROCESS_PARAMETER_VALUE AS INT )" );
            rs = pstmt.executeQuery();

            while ( rs.next() )
            {
                costCodeMaster = new CostCodeMaster();

                costCodeMaster.setAutoId    ( rs.getInt( "AUTO_ID" ) );
                costCodeMaster.setPinCount  ( rs.getString( "PROCESS_PARAMETER_VALUE" ) );
                costCodeMaster.setCostCode  ( rs.getString( "COST_CODE" ) );
                costCodeMaster.setType      ( "PIN" );

                pinCountList.add( costCodeMaster );
            }
        }
        catch ( SQLException sql)
        {
            sql.printStackTrace();
            throw new ScalarException( "Could not get PIN Cost Code Details ", sql );
        }
        finally
        {
            if ( rs != null )
            {
                try { rs.close(); } catch (SQLException igonore) { }
            }

            if ( pstmt != null )
            {
                try { pstmt.close(); } catch (SQLException igonore) { }
            }

            if ( conn != null )
            {
                try { conn.close(); } catch (SQLException igonore) { }
            }
        }
        return pinCountList;
    }

    public static ArrayList loadWaferPartNumber( DBTransaction dbTransaction ) throws ScalarException
    {
        Connection conn             = null;
        ResultSet rs                = null;
        PreparedStatement pstmt     = null;
        CostCodeMaster costCodeMaster = null;
        ArrayList waferPartNoList    = new ArrayList();

        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement( "SELECT DISTINCT ISNULL( COST_CODE_ID, -2 ) AS AUTO_ID, P.PART_NUMBER, BCC.COST_CODE FROM PRODUCT P INNER JOIN PROCESS_STEP PS ON P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND PS_TYPE  = 'FAB' LEFT OUTER JOIN COST_CODE_MASTER BCC ON  P.PART_NUMBER = BCC.COST_CODE_NO AND BCC.COST_CODE_TYPE='WAFER' ORDER BY P.PART_NUMBER" );
            rs = pstmt.executeQuery();

            while ( rs.next() )
            {
                costCodeMaster = new CostCodeMaster();

                costCodeMaster.setAutoId            ( rs.getInt( "AUTO_ID" ) );
                costCodeMaster.setWaferPartNumber   ( rs.getString( "PART_NUMBER" ) );
                costCodeMaster.setCostCode          ( rs.getString( "COST_CODE" ) );
                costCodeMaster.setType              ( "WAFER" );

                waferPartNoList.add( costCodeMaster );
            }
        }
        catch ( SQLException sql)
        {
            sql.printStackTrace();
            throw new ScalarException( "Could not get Wafer Part Number Cost Code Details ", sql );
        }
        finally
        {
            if ( rs != null )
            {
                try { rs.close(); } catch (SQLException igonore) { }
            }

            if ( pstmt != null )
            {
                try { pstmt.close(); } catch (SQLException igonore) { }
            }

            if ( conn != null )
            {
                try { conn.close(); } catch (SQLException igonore) { }
            }
        }
        return waferPartNoList;
    }

    /*public static void saveCostCodeMaster ( DBTransaction dbTransaction, ArrayList costCodeList ) throws ScalarException
    {
        Connection conn                 = null;
		CallableStatement cstmt         = null;
        CostCodeMaster costCodeMaster   = null;
        boolean success                 = false;

        try
        {
            if( dbTransaction == null )
			{
				conn = DataSource.getConnection();
			}
			else
			{
				conn = dbTransaction.getConnection();
			}

            cstmt = conn.prepareCall ( "{ CALL SP_CREATE_UPDATE_MASTER_COST_CODES( ?, ?, ?, ? ) }" );

            if( costCodeList != null && costCodeList.isEmpty() == false )
            {
                for( int i = 0; i < costCodeList.size(); i++ )
                {
                    costCodeMaster = ( CostCodeMaster ) costCodeList.get( i );

                    System.out.println ("costCodeMaster.getAutoId() = " + costCodeMaster.getAutoId());
                    System.out.println ("costCodeMaster.getType() = " + costCodeMaster.getType());
                    System.out.println ("costCodeMaster.getCostCode() = " + costCodeMaster.getCostCode());
                    System.out.println ("costCodeMaster.getWaferPartNumber() = " + costCodeMaster.getWaferPartNumber());
                    System.out.println ("costCodeMaster.getPackageType() = " + costCodeMaster.getPackageType());
                    System.out.println ("costCodeMaster.getPinCount() = " + costCodeMaster.getPinCount());

                    cstmt.setInt    ( 1, costCodeMaster.getAutoId() );
                    cstmt.setString ( 2, costCodeMaster.getType() );

                    if( "WAFER".equalsIgnoreCase( costCodeMaster.getType() ) )
                    {
                        cstmt.setString ( 3, costCodeMaster.getWaferPartNumber() );
                    }
                    else if( "PACKAGE".equalsIgnoreCase( costCodeMaster.getType() ))
                    {
                        cstmt.setString ( 3, costCodeMaster.getPackageType() );
                    }
                    else if( "PIN".equalsIgnoreCase( costCodeMaster.getType() )   )
                    {
                        cstmt.setString ( 3, costCodeMaster.getPinCount() );
                    }
                    cstmt.setString ( 4, costCodeMaster.getCostCode() );
                    cstmt.addBatch();
                    }    
                }
                cstmt.executeBatch();

            success = true;
        }
        catch( Exception e )
        {
            e.printStackTrace();
			throw new ScalarException ( "Could not Save the Cost Code Master Details into database.");
        }
        finally
		{
			if ( dbTransaction == null )
			{
				try
				{
					if (conn != null)
					{
                        if( success == true )
                        {
						    conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
					}
				}
                catch (SQLException ignore) {System.out.println("Sql exception ");}
			}
			if ( cstmt != null )
			{
                try { cstmt.close(); } catch (SQLException ignore){}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch (SQLException ignore){}
            }
	    }

    }*/

    public static void saveCostCodeMaster ( DBTransaction dbTransaction, ArrayList costCodeList ) throws ScalarException
    {
        Connection conn                 = null;
		CallableStatement cstmt         = null;
        CostCodeMaster costCodeMaster   = null;
        boolean success                 = false;

        try
        {
            if( dbTransaction == null )
			{
				conn = DataSource.getConnection();
			}
			else
			{
				conn = dbTransaction.getConnection();
			}
            cstmt = conn.prepareCall ( "{ CALL SP_CREATE_UPDATE_COST_CODE_MASTER ( ?, ?, ?, ? ) }" );
            if( costCodeList != null && costCodeList.isEmpty() == false )
            {
                for( int i = 0; i < costCodeList.size(); i++ )
                {
                    costCodeMaster = ( CostCodeMaster ) costCodeList.get( i );

                    cstmt.setInt    ( 1, costCodeMaster.getAutoId() );
                    cstmt.setString ( 2, costCodeMaster.getType() );

                    if( "WAFER".equalsIgnoreCase( costCodeMaster.getType() ) )
                    {
                        cstmt.setString ( 3, costCodeMaster.getWaferPartNumber() );
                    }
                    else if( "PACKAGE".equalsIgnoreCase( costCodeMaster.getType() ))
                    {
                        cstmt.setString ( 3, costCodeMaster.getPackageType() );
                    }
                    else if( "PIN".equalsIgnoreCase( costCodeMaster.getType() )   )
                    {
                        cstmt.setString ( 3, costCodeMaster.getPinCount() );
                    }
                    cstmt.setString ( 4, costCodeMaster.getCostCode() );
                    cstmt.addBatch();
                    }
                }
                cstmt.executeBatch();

            success = true;
        }
        catch( Exception e )
        {
            e.printStackTrace();
			throw new ScalarException ( "Could not Save the Cost Code Master Details into database.");
        }
        finally
		{
			if ( dbTransaction == null )
			{
				try
				{
					if (conn != null)
					{
                        if( success == true )
                        {
						    conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
					}
				}
                catch (SQLException ignore) {System.out.println("Sql exception ");}
			}
			if ( cstmt != null )
			{
                try { cstmt.close(); } catch (SQLException ignore){}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch (SQLException ignore){}
            }
	    }

    }

    public static void updateProductCostCodes ( DBTransaction dbTransaction ) throws ScalarException
    {
        Connection conn                 = null;
		CallableStatement cstmt         = null;
        boolean success                 = false;

        try
        {
            if( dbTransaction == null )
			{
				conn = DataSource.getConnection();
			}
			else
			{
				conn = dbTransaction.getConnection();
			}

            cstmt = conn.prepareCall ( "{ CALL SP_UPDATE_PRODUCT_COST_CODE() }" );

            cstmt.execute();

            success = true;
        }
        catch( Exception e )
        {
            e.printStackTrace();
			throw new ScalarException ( "Could not update the Cost Codes Into Product Table." );
        }
        finally
		{
			if ( dbTransaction == null )
			{
				try
				{
					if (conn != null)
					{
                        if( success == true )
                        {
						    conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
					}
				}
                catch (SQLException ignore) {System.out.println("Sql exception ");}
			}
			if ( cstmt != null )
			{
                try { cstmt.close(); } catch (SQLException ignore){}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch (SQLException ignore){}
            }
	    }

    }


}
